﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using NBearLite;
using Xin478.CSWork.Common;

namespace Xin478.CSWork.DomainModel
{
    public class WorkModel
    {
        public static IList<WorkInfo> GetAll(Database db)
        {
            return db.Select(MyWork.Works).ToList<WorkInfo>();
        }

        public static IList<WorkInfo> GetNew(Database db, int userId)
        {
            return  GetNew( db, userId,30);
        }

        public static IList<WorkInfo> GetNew(Database db,int userId, int max)
        {
            IList<WorkInfo> wis=null;
            IDataReader dr = db.Select(MyWork.Works,
                MyWork.Works.Id,
                MyWork.Works.Title,
                MyWork.Works.PublisherId,
                MyWork.Works.UserId,
                MyWork.Works.Weight,
                MyWork.Works.AddTime,
                MyWork.Works.FinishTime,
                MyWork.Works.LatestTime,
                MyWork.Employees.Name
                )
                .LeftJoin(MyWork.Employees, MyWork.Employees.Id == MyWork.Works.PublisherId)
                .SetSelectRange(max, 0, MyWork.Works.Id)
                .Where(MyWork.Works.UserId == userId && MyWork.Works.AcceptTime == null)
                .ToDataReader();
            
            if(dr.Read())
            {
                wis = new List<WorkInfo>();
                do
                {
                    WorkInfo wi = new WorkInfo();
                    wi.Id = Convert.ToInt32(dr["Id"]);
                    wi.Title=dr["Title"].ToString();
                    wi.PublisherId= Convert.ToInt32(dr["PublisherId"]);                    
                    wi.UserId= Convert.ToInt32(dr["UserId"]);
                    wi.Weight= Convert.ToInt32(dr["Weight"]);
                    if(dr["AddTime"]!=DBNull.Value)  wi.AddTime= Convert.ToDateTime(dr["AddTime"]);
                    if (dr["FinishTime"] != DBNull.Value) wi.FinishTime = Convert.ToDateTime(dr["FinishTime"]);
                    if (dr["LatestTime"] != DBNull.Value) wi.LatestTime = Convert.ToDateTime(dr["LatestTime"]);
                    wi.PublisherInfo = new EmployeeInfo();
                    wi.PublisherInfo.Name = dr["Name"].ToString();
                    wi.PublisherInfo.Id = wi.PublisherId;
                    wis.Add(wi);

                } while (dr.Read());
                foreach (WorkInfo wi in wis)
                {
                    wi.WorkCommandInfos = db.Select(MyWork.WorkCommands).Where(MyWork.WorkCommands.WorkId == wi.Id).ToList<WorkCommandInfo>();
                }
            }
            dr.Close();

            return wis;
        }

        public static IList<WorkInfo> GetWorking(Database db, int userId, int afterWorkId)
        {
            return GetWorking(db, userId, afterWorkId, 10);
        }

        public static IList<WorkInfo> GetWorking(Database db, int userId,int afterWorkId, int max)
        {
            IList<WorkInfo> wis = null;
            IDataReader dr = db.Select(MyWork.Works,
                MyWork.Works.Id,
                MyWork.Works.Title,
                MyWork.Works.PublisherId,
                MyWork.Works.UserId,
                MyWork.Works.Weight,
                MyWork.Works.AddTime,
                MyWork.Works.FinishTime,
                MyWork.Works.LatestTime,
                MyWork.Employees.Name
                )
                .LeftJoin(MyWork.Employees, MyWork.Employees.Id == MyWork.Works.PublisherId)
                .SetSelectRange(max, 0, MyWork.Works.Id)
                .Where(MyWork.Works.UserId == userId && MyWork.Works.FinishTime == null && MyWork.Works.Id>afterWorkId)
                .OrderBy(MyWork.Works.Id.Asc)
                .ToDataReader();

            if (dr.Read())
            {
                wis = new List<WorkInfo>();
                do
                {
                    WorkInfo wi = new WorkInfo();
                    wi.Id = Convert.ToInt32(dr["Id"]);
                    wi.Title = dr["Title"].ToString();
                    wi.PublisherId = Convert.ToInt32(dr["PublisherId"]);
                    wi.UserId = Convert.ToInt32(dr["UserId"]);
                    wi.Weight = Convert.ToInt32(dr["Weight"]);
                    if (dr["AddTime"] != DBNull.Value) wi.AddTime = Convert.ToDateTime(dr["AddTime"]);
                    if (dr["FinishTime"] != DBNull.Value) wi.FinishTime = Convert.ToDateTime(dr["FinishTime"]);
                    if (dr["LatestTime"] != DBNull.Value) wi.LatestTime = Convert.ToDateTime(dr["LatestTime"]);
                    wi.PublisherInfo = new EmployeeInfo();
                    wi.PublisherInfo.Name = dr["Name"].ToString();
                    wi.PublisherInfo.Id = wi.PublisherId;

                    wi.WorkCommandInfos = db.Select(MyWork.WorkCommands).Where(MyWork.WorkCommands.WorkId == wi.Id).ToList<WorkCommandInfo>();
                    wi.WorkFileInfos = db.Select(MyWork.WorkFiles, 
                        MyWork.WorkFiles.Id,
                        MyWork.WorkFiles.FileName)
                        .Where(MyWork.WorkFiles.WorkId == wi.Id).ToList<WorkFileInfo>();

                    wis.Add(wi);
                } while (dr.Read());
            }
            dr.Close();


            return wis;
        }

        public static IList<WorkInfo> GetSycList(Database db, int userId, int afterWorkId, int pageSize)
        {
            IList<WorkInfo> wis = null;
            IDataReader dr = db.Select(MyWork.Works,
                MyWork.Works.Id,
                MyWork.Works.Title,
                MyWork.Works.PublisherId,
                MyWork.Works.UserId,
                MyWork.Works.Weight,
                MyWork.Works.AddTime,
                MyWork.Works.FinishTime,
                MyWork.Works.LatestTime
                )
                .SetSelectRange(pageSize, 0, MyWork.Works.Id)
                .Where( (MyWork.Works.UserId == userId || MyWork.Works.PublisherId==userId) && MyWork.Works.Id > afterWorkId)
                .OrderBy(MyWork.Works.Id.Asc)
                .ToDataReader();

            if (dr.Read())
            {
                wis = new List<WorkInfo>();
                do
                {
                    WorkInfo wi = new WorkInfo();
                    wi.Id = Convert.ToInt32(dr["Id"]);
                    wi.Title = dr["Title"].ToString();
                    wi.PublisherId = Convert.ToInt32(dr["PublisherId"]);
                    wi.UserId = Convert.ToInt32(dr["UserId"]);
                    wi.Weight = Convert.ToInt32(dr["Weight"]);
                    if (dr["AddTime"] != DBNull.Value) wi.AddTime = Convert.ToDateTime(dr["AddTime"]);
                    if (dr["FinishTime"] != DBNull.Value) wi.FinishTime = Convert.ToDateTime(dr["FinishTime"]);
                    if (dr["LatestTime"] != DBNull.Value) wi.LatestTime = Convert.ToDateTime(dr["LatestTime"]);
                    //wi.PublisherInfo = new EmployeeInfo();
                    //wi.PublisherInfo.Name = dr["Name"].ToString();
                    //wi.PublisherInfo.Id = wi.PublisherId;

                    wi.WorkCommandInfos = db.Select(MyWork.WorkCommands).Where(MyWork.WorkCommands.WorkId == wi.Id).ToList<WorkCommandInfo>();
                    wi.WorkFileInfos = db.Select(MyWork.WorkFiles).Where(MyWork.WorkFiles.WorkId == wi.Id).ToList<WorkFileInfo>();

                    wis.Add(wi);
                } while (dr.Read());
            }
            dr.Close();


            return wis;
        }

        public static DataTable GetTable(Database db, int from, int pageSize, WhereClip where, QueryColumn orderBy, bool isAsc)
        {
            DataSet ds= db.Select(MyWork.Works,
                MyWork.Works.Id,
                MyWork.Works.Weight,
                MyWork.Works.Title,
                MyWork.Employees.__Alias("pubEmployees").Name.Alias("PublisherName"),
                MyWork.Employees.__Alias("getEmployees").Name.Alias("UserName"),
                MyWork.Works.AddTime,
                MyWork.Works.AcceptTime,
                MyWork.Works.FinishTime,
                MyWork.Works.LatestTime,
                MyWork.Works.PublisherId,
                MyWork.Works.PublisherId,
                MyWork.Works.UserId
                )
                .LeftJoin(MyWork.Employees, "getEmployees", MyWork.Employees.__Alias("getEmployees").Id == MyWork.Works.UserId)
                .LeftJoin(MyWork.Employees, "pubEmployees", MyWork.Employees.__Alias("pubEmployees").Id == MyWork.Works.PublisherId)
                .Where(where)
                .SetSelectRange(pageSize, from, orderBy)
                .OrderBy(isAsc ? orderBy.Asc : orderBy.Desc)
                .ToDataSet();


            if (ds == null || ds.Tables.Count == 0) return null;
            else return ds.Tables[0];
        }

        public static IList<WorkInfo> GetList(Database db, int from, int pageSize, WhereClip where, QueryColumn orderBy, bool isAsc)
        {
            IDataReader dr = db.Select(MyWork.Works,
                MyWork.Works.Id,
                MyWork.Works.Weight,
                MyWork.Works.Title,
                MyWork.Employees.__Alias("pubEmployees").Name.Alias("PublisherName"),
                MyWork.Employees.__Alias("getEmployees").Name.Alias("UserName"),
                MyWork.Works.AddTime,
                MyWork.Works.AcceptTime,
                MyWork.Works.FinishTime,
                MyWork.Works.LatestTime,
                MyWork.Works.PublisherId,
                MyWork.Works.UserId
                )
                .LeftJoin(MyWork.Employees, "getEmployees", MyWork.Employees.__Alias("getEmployees").Id == MyWork.Works.UserId)
                .LeftJoin(MyWork.Employees, "pubEmployees", MyWork.Employees.__Alias("pubEmployees").Id == MyWork.Works.PublisherId)
                .Where(where)
                .SetSelectRange(pageSize, from, orderBy)
                .OrderBy(isAsc ? orderBy.Asc : orderBy.Desc)
                .ToDataReader();
            IList<WorkInfo> list = new List<WorkInfo>();
            while (dr.Read())
            {
                WorkInfo wi = new WorkInfo();
                wi.Id = dr.GetInt32(0);
                wi.Weight = dr.GetInt32(1);
                wi.Title = dr["Title"].ToString();
                wi.PublisherId = dr.GetInt32(9);
                wi.UserId = dr.GetInt32(10);

                if (dr["AddTime"] != DBNull.Value) wi.AddTime = Convert.ToDateTime(dr["AddTime"]);
                if (dr["FinishTime"] != DBNull.Value) wi.FinishTime = Convert.ToDateTime(dr["FinishTime"]);
                if (dr["LatestTime"] != DBNull.Value) wi.LatestTime = Convert.ToDateTime(dr["LatestTime"]);
                wi.PublisherInfo = new EmployeeInfo();
                wi.PublisherInfo.Name = dr["PublisherName"].ToString();
                wi.PublisherInfo.Id = wi.PublisherId;
                wi.UserInfo = new EmployeeInfo();
                wi.UserInfo.Name = dr["UserName"].ToString();
                list.Add(wi);
            }
            return list;
        }

        public static bool UpdateAcceptTime(Database db, int workId, DateTime acceptTime, int userId)
        {
            return db.Update(MyWork.Works).AddColumn(MyWork.Works.AcceptTime, acceptTime).Where(MyWork.Works.Id == workId && MyWork.Works.UserId == userId).Execute() == 1;
        }

        public static WorkInfo GetOne(Database db, int workId)
        {
            WorkInfo wi = db.Select(MyWork.Works).Where(MyWork.Works.Id == workId).ToSingleObject<WorkInfo>();

            if (wi!=null)
            {
                wi.PublisherInfo = EmployeeModel.Select(db, wi.PublisherId);
                wi.UserInfo = EmployeeModel.Select(db, wi.UserId);
                //wi.WorkFileInfos = db.Select(MyWork.WorkFiles).Where(MyWork.WorkFiles.WorkId == wi.Id).ToList<WorkFileInfo>();
                wi.WorkCommandInfos = db.Select(MyWork.WorkCommands).Where(MyWork.WorkCommands.WorkId == wi.Id).ToList<WorkCommandInfo>();
            }

            return wi;
        }

        public static WorkInfo GetInfo(Database db, int workId)
        {
            return db.Select(MyWork.Works).Where(MyWork.Works.Id == workId).ToSingleObject<WorkInfo>();
        }


        public static IList<WorkFileInfo> GetOneFiles(Database db, int workId)
        {
            return db.Select(MyWork.WorkFiles).Where(MyWork.WorkFiles.WorkId == workId).OrderBy(MyWork.WorkFiles.Id.Desc).ToList<WorkFileInfo>();
        }

        public static WorkFileInfo GetOneFile(Database db, int fileId)
        {
            return db.Select(MyWork.WorkFiles).Where(MyWork.WorkFiles.Id == fileId).ToSingleObject<WorkFileInfo>();
        }
        
    }
}
